-- create database projeto;

-- adcionando plpgsql
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
  HANDLER plpgsql_call_handler
  VALIDATOR plpgsql_validator;
ALTER LANGUAGE plpgsql OWNER TO postgres;

/*
ALTER TABLE Profissao DROP CONSTRAINT FKProfissao66504;
ALTER TABLE Especializacao DROP CONSTRAINT FKEspecializ744318;
ALTER TABLE Especializacao DROP CONSTRAINT FKEspecializ926790;
ALTER TABLE Solicitacao DROP CONSTRAINT FKSolicitaca741811;
ALTER TABLE Solicitacao DROP CONSTRAINT FKSolicitaca640088;
ALTER TABLE ClientesProfissional DROP CONSTRAINT FKClientesPr183846;
ALTER TABLE ClientesProfissional DROP CONSTRAINT FKClientesPr72559;
DROP TABLE Area;
DROP TABLE Profissao;
DROP TABLE Profissional;
DROP TABLE Especializacao;
DROP TABLE Cliente;
DROP TABLE Solicitacao;
DROP TABLE ClientesProfissional;
--*/

-- criando tabelas
CREATE TABLE Area (
  CodArea    SERIAL NOT NULL, 
  Descricao dm_nome, 
  PRIMARY KEY (CodArea));
CREATE TABLE Profissao (
  CodProfissao  SERIAL NOT NULL, 
  Descricao    dm_nome UNIQUE, 
  AreaCodArea  int4 NOT NULL, 
  PRIMARY KEY (CodProfissao));
CREATE TABLE Profissional (
  CodProfissional       SERIAL NOT NULL, 
  CPF                  dm_CPF NULL UNIQUE, 
  Nome                 dm_nome, 
  Fantasia             dm_nome2, 
  Sexo                 char(1) NOT NULL, 
  FoneFixo             dm_telefone, 
  FoneCel              dm_telefone, 
  Endereco             dm_local, 
  Bairro               dm_local, 
  Cidade               dm_local, 
  UF                   dm_UF, 
  RG_Num               dm_RG, 
  RG_Org_Exp           char(6) NOT NULL, 
  RG_Data_Exp          date NOT NULL, 
  CarteiraProfissional varchar(14) NOT NULL, 
  CNH_Registro         varchar(12), 
  CNH_Cat              varchar(10), 
  CNH_Vencto           date, 
  DataNascimento       date NOT NULL, 
  Alocado              bool NOT NULL, 
  PRIMARY KEY (CodProfissional),
  -- CHECKS
  CONSTRAINT valida_sexo CHECK (sexo = 'm' or sexo = 'f'),
  CONSTRAINT valida_data CHECK (DataNascimento < current_date)
  
  );
CREATE TABLE Especializacao (
  ProfissionalCodProfissional int4 NOT NULL, 
  ProfissaoCodProfissao       int4 NOT NULL, 
  DataEspecializacao          date NOT NULL, 
  Nivel                       int2 NOT NULL, 
  PRIMARY KEY (ProfissionalCodProfissional, 
  ProfissaoCodProfissao));
CREATE TABLE Cliente (
  CodCliente          BIGSERIAL NOT NULL, 
  NomeCli            dm_nome, 
  Fantasia           dm_nome2, 
  Endereco           dm_local,
  Bairro             dm_local,
  Cidade             dm_local,
  UF                 dm_UF, 
  FoneFixo           dm_telefone, 
  FoneFax            dm_telefone, 
  FoneCelular        dm_telefone, 
  Email              dm_local,
  HomePage           dm_local,
  Tipo               char(1) NOT NULL, 
  DataNasctoAbertura date, 
  CPFCNPJ            varchar(18) UNIQUE, 
  RGIE               varchar(18), 
  PRIMARY KEY (CodCliente));
CREATE TABLE Solicitacao (
  CodSolicitacao         BIGSERIAL NOT NULL, 
  ClienteCodCliente     int8 NOT NULL, 
  ProfissaoCodProfissao int4 NOT NULL, 
  Qtde                  int4, 
  DataSolicitacao       date, 
  DataAtendimento       date, 
  PRIMARY KEY (CodSolicitacao));
CREATE TABLE ClientesProfissional (
  SolicitacaoCodSolicitacao   int8 NOT NULL, 
  ProfissionalCodProfissional int4 NOT NULL DEFAULT 3, 
  Aceito                      bool, 
  Nivel                       int2 NOT NULL, 
  PRIMARY KEY (SolicitacaoCodSolicitacao, 
  ProfissionalCodProfissional));
	id	serial primary key,
create table users (
	id	serial primary key,
	nome	dm_nome,
	tipo	char(1) check (tipo = 'C' or tipo = 'P' or tipo = 'O'),
	codigo	integer,
	email	dm_local,
	ativo	boolean default true,
	login	varchar(10),
	senha	varchar(15) default ''
)
ALTER TABLE Profissao ADD CONSTRAINT FKProfissao66504 FOREIGN KEY (AreaCodArea) REFERENCES Area (CodArea);
ALTER TABLE Especializacao ADD CONSTRAINT FKEspecializ744318 FOREIGN KEY (ProfissionalCodProfissional) REFERENCES Profissional (CodProfissional);
ALTER TABLE Especializacao ADD CONSTRAINT FKEspecializ926790 FOREIGN KEY (ProfissaoCodProfissao) REFERENCES Profissao (CodProfissao);
ALTER TABLE Solicitacao ADD CONSTRAINT FKSolicitaca741811 FOREIGN KEY (ClienteCodCliente) REFERENCES Cliente (CodCliente);
ALTER TABLE Solicitacao ADD CONSTRAINT FKSolicitaca640088 FOREIGN KEY (ProfissaoCodProfissao) REFERENCES Profissao (CodProfissao);
ALTER TABLE ClientesProfissional ADD CONSTRAINT FKClientesPr183846 FOREIGN KEY (SolicitacaoCodSolicitacao) REFERENCES Solicitacao (CodSolicitacao);
ALTER TABLE ClientesProfissional ADD CONSTRAINT FKClientesPr72559 FOREIGN KEY (ProfissionalCodProfissional) REFERENCES Profissional (CodProfissional);




